

alter         function dbo.f_msemployeegetpriorinfo(
  @moneyid integer,
  @empid integer,
  @subjectid integer,
  @nyear integer,
  @nmonth integer,
  @filid integer
)
returns 
  @temp_table table (
    nbalyear integer ,
    nbalmonth integer ,
    bmbala decimal(18,2),
    bysumcredit decimal(18,2),
    bysumdebit decimal(18,2),
    tmsumcredit decimal(18,2),
    tmsumdebit decimal(18,2),
    tmsum decimal(18,2),
    fbmbala decimal(18,2),
    fbysumcredit decimal(18,2),
    fbysumdebit decimal(18,2),
    ftmsumcredit decimal(18,2),
    ftmsumdebit decimal(18,2),
    ftmsum decimal(18,2)
  )
as
begin
  declare @nbalyear integer
  declare @nbalmonth integer
  declare @maxyear integer
  declare @maxmonth integer
  declare @newfilid integer
  declare @bmbala decimal(18,2) 
  declare @bysumcredit decimal(18,2)
  declare @bysumdebit decimal(18,2)
  declare @tmsumcredit decimal(18,2)
  declare @tmsumdebit decimal(18,2)
  declare @tmsum decimal(18,2)
  declare @fbmbala decimal(18,2)
  declare @fbysumcredit decimal(18,2)
  declare @fbysumdebit decimal(18,2)
  declare @ftmsumcredit decimal(18,2)
  declare @ftmsumdebit decimal(18,2)
  declare @ftmsum decimal(18,2)
  declare @odata integer

  declare @sum_table table (
    nbalyear integer,
    nbalmonth integer,
    filid integer,
    bmbala double precision,
    bysumcredit double precision,
    bysumdebit double precision,
    tmsumcredit double precision,
    tmsumdebit double precision,
    tmsum double precision,
    fbmbala double precision,
    fbysumcredit double precision,
    fbysumdebit double precision,
    ftmsumcredit double precision,
    ftmsumdebit double precision,
    ftmsum double precision
  )

  declare cur_filiale cursor local forward_only static read_only
    for select fi_filid from td_filiale where fi_filid = @filid or @filid = -99

  open cur_filiale 
  fetch next from  cur_filiale into @newfilid
  while @@fetch_status = 0 
  begin   
    set @nbalyear     = 0
    set @nbalmonth    = 0
    set @bmbala       = 0.00
    set @bysumcredit  = 0.00
    set @bysumdebit   = 0.00
    set @tmsumcredit  = 0.00
    set @tmsumdebit   = 0.00
    set @tmsum        = 0.00
    set @fbmbala      = 0.00
    set @fbysumcredit = 0.00
    set @fbysumdebit  = 0.00
    set @ftmsumcredit = 0.00
    set @ftmsumdebit  = 0.00
    set @ftmsum       = 0.00

    select @odata=count(subjectid) from tft_msemployee 
      where empid = @empid and subjectid = @subjectid
        and (@moneyid is null or moneyid = @moneyid)
        and ((nyear < @nyear) or (nyear = @nyear and nmonth < @nmonth))
        and filid = @newfilid and isnull(odata,0)=0


    if(@odata > 0)
    begin
      select top 1 @nbalyear     = nyear,
                   @nbalmonth    = nmonth,
                   @bmbala       = bmbala,
                   @bysumcredit  = bysumcredit,
                   @bysumdebit   = bysumdebit,
                   @tmsumcredit  = tmsumcredit,
                   @tmsumdebit   = tmsumdebit,
                   @tmsum        = tmsum,
                   @fbmbala      = fbmbala,
                   @fbysumcredit = fbysumcredit,
                   @fbysumdebit  = fbysumdebit,
                   @ftmsumcredit = ftmsumcredit,
                   @ftmsumdebit  = ftmsumdebit,
                   @ftmsum       = ftmsum
      from tft_msemployee
      where empid = @empid and subjectid = @subjectid
        and (@moneyid is null or moneyid = @moneyid) 
        and ((nyear < @nyear) or (nyear = @nyear and nmonth < @nmonth))
        and filid = @newfilid  and isnull(odata,0)=0
      order by nyear desc, nmonth desc
    end
    else
    begin
      select top 1 @nbalyear     = nyear,
                   @nbalmonth    = nmonth,
                   @bmbala       = bmbala,
                   @bysumcredit  = bysumcredit,
                   @bysumdebit   = bysumdebit,
                   @tmsumcredit  = tmsumcredit,
                   @tmsumdebit   = tmsumdebit,
                   @tmsum        = tmsum,
                   @fbmbala      = fbmbala,
                   @fbysumcredit = fbysumcredit,
                   @fbysumdebit  = fbysumdebit,
                   @ftmsumcredit = ftmsumcredit,
                   @ftmsumdebit  = ftmsumdebit,
                   @ftmsum       = ftmsum
      from tft_msemployee
      where empid = @empid and subjectid = @subjectid
        and ((nyear < @nyear) or (nyear = @nyear and nmonth < @nmonth))
        and filid = @newfilid and isnull(odata,0)=1
      order by nyear desc, nmonth desc
    end
    if (@bmbala       is null) set @bmbala       = 0.00;
    if (@bysumcredit  is null) set @bysumcredit  = 0.00;
    if (@bysumdebit   is null) set @bysumdebit   = 0.00;
    if (@tmsumcredit  is null) set @tmsumcredit  = 0.00;
    if (@tmsumdebit   is null) set @tmsumdebit   = 0.00;
    if (@tmsum        is null) set @tmsum        = 0.00;
    if (@fbmbala      is null) set @fbmbala      = 0.00;
    if (@fbysumcredit is null) set @fbysumcredit = 0.00;
    if (@fbysumdebit  is null) set @fbysumdebit  = 0.00;
    if (@ftmsumcredit is null) set @ftmsumcredit = 0.00;
    if (@ftmsumdebit  is null) set @ftmsumdebit  = 0.00;
    if (@ftmsum       is null) set @ftmsum       = 0.00;

    if (@nbalyear <> @nyear)
    begin
      set @bysumcredit  = 0.00;
      set @bysumdebit   = 0.00;
      set @fbysumcredit = 0.00;
      set @fbysumdebit  = 0.00;
    end

    insert into @sum_table values(@nbalyear, @nbalmonth, @newfilid, @bmbala,  
                                  @bysumcredit, @bysumdebit, @tmsumcredit,
                                  @tmsumdebit, @tmsum, @fbmbala, @fbysumcredit,  
                                  @fbysumdebit, @ftmsumcredit, @ftmsumdebit, @ftmsum);
    
    update @sum_table set bysumcredit = 0.00, bysumdebit = 0.00,
                          fbysumcredit = 0.00, fbysumdebit = 0.00 
      where nbalyear <> @nyear and filid = @newfilid
    fetch next from cur_filiale into @newfilid;
  end
  close cur_filiale;
  deallocate  cur_filiale

  select @maxyear  = max(nbalyear)  from @sum_table
  select @maxmonth = max(nbalmonth) from @sum_table where nbalyear = @maxyear

  declare cur_tmp  cursor local forward_only static read_only
    for select nbalyear, nbalmonth, sum(bmbala), sum(bysumcredit), sum(bysumdebit),
      sum(tmsumcredit), sum(tmsumdebit), sum(tmsum), sum(fbmbala), sum(fbysumcredit),
      sum(fbysumdebit), sum(ftmsumcredit), sum(ftmsumdebit), sum(ftmsum)
    from @sum_table group by nbalyear, nbalmonth order by nbalyear desc, nbalmonth desc
  open cur_tmp 
  fetch next from cur_tmp into @nbalyear, @nbalmonth, @bmbala,  
                               @bysumcredit, @bysumdebit, @tmsumcredit,
                               @tmsumdebit, @tmsum, @fbmbala, @fbysumcredit,  
                               @fbysumdebit, @ftmsumcredit, @ftmsumdebit, @ftmsum;
  while @@fetch_status = 0 
  begin   
    if (@nbalyear = @maxyear and @nbalmonth = @maxmonth)  
    begin
      insert into @temp_table values(@nbalyear, @nbalmonth, @bmbala, @bysumcredit,
                                   @bysumdebit, @tmsumcredit, @tmsumdebit,
                                   @tmsum, @fbmbala, @fbysumcredit, @fbysumdebit,
                                   @ftmsumcredit, @ftmsumdebit, @ftmsum);
    end
    if (@nbalyear <> @maxyear or @nbalmonth <> @maxmonth)
    begin
        update @temp_table set bmbala = bmbala + @bmbala + @tmsumdebit - @tmsumcredit,
                               fbmbala = fbmbala + @fbmbala + @ftmsumdebit - @ftmsumcredit
        where nbalyear = @maxyear and nbalmonth = @maxmonth;
    end
    fetch next from cur_tmp into @nbalyear, @nbalmonth, @bmbala,
                                  @bysumcredit, @bysumdebit, @tmsumcredit,
                                  @tmsumdebit, @tmsum, @fbmbala, @fbysumcredit,
                                  @fbysumdebit, @ftmsumcredit, @ftmsumdebit, @ftmsum
  end
  close cur_tmp
  deallocate  cur_tmp
  return

end







